import pymysql
import hashlib


class DataBase:
    def __init__(self,
                 host='192.168.0.101',
                 port=3306,
                 user='root',
                 password="123456",
                 charset="utf8mb4",
                 database=None):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self.connect_sql()

    def connect_sql(self):
        self.db = pymysql.connect(host=self.host,
                                  port=self.port,
                                  user=self.user,
                                  password=self.password,
                                  database=self.database,
                                  charset=self.charset)

    # 关闭数据库
    def close(self):
        self.db.close()

    # 创建游标
    def create_cursor(self):
        self.cursor = self.db.cursor()

    # 注册
    def register(self, name, password):
        new_password = self.__encryption(password)
        # 查询数据库
        sql_search = "select * from user where name='%s'" % name
        self.cursor.execute(sql_search)
        r = self.cursor.fetchone()
        if r:
            return False
        sql_insert = "insert into user(name,password) values(%s,%s)"
        try:
            self.cursor.execute(sql_insert, [name, new_password])
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            print(e)
            return False

    # 查询单词
    def query(self, word,name):
        sql_search_mean = "select mean from dictionary where word='%s'"%word
        self.cursor.execute(sql_search_mean)
        cursor_mean = self.cursor.fetchone()
        if cursor_mean:
            sql_add_history = "insert into history(name,word) values(%s,%s)"
            try:
                self.cursor.execute(sql_add_history, [name,word])
                self.db.commit()
            except Exception as e:
                self.db.rollback()
                print(e)
            return cursor_mean[0]
        else:
            return False


    # 登录
    def login(self, name, password):
        new_password = self.__encryption(password)
        sql_search = "select * from user where name=%s and password=%s"
        self.cursor.execute(sql_search, [name, new_password])
        r = self.cursor.fetchone()
        # 有数据则允许登录
        if r:
            return True
        else:
            return False

    # 密码加密
    def __encryption(self, password):
        salt = "#*06#"
        hash = hashlib.md5(salt.encode())
        hash.update(password.encode())
        new_password = hash.hexdigest()
        return new_password


    def history(self,name):
        sql_search = "select name,word,time from history where name='%s'order by time desc limit 10 "%name
        self.cursor.execute(sql_search)
        r = self.cursor.fetchall()
        if r:
            res=""
            for item in r:
                res+="用户名:%s单词:%-10s 查询时间:%s"%item
                res+="\n"
            return res
        else:
            return False


if __name__ == '__main__':
    data_base = DataBase(database='userlist')
    data_base.create_cursor()
    print(data_base.history("weijunjie"))
